/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.h2database;
import Account.Account;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.SelectArg;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.h2.tools.Server;

/**
 *
 * @author asus
 */
public class App {
    static final String DRIVER_CLASS = "org.h2.Driver";
    
    static final String JDBC_URL = "jdbc:h2:/F:/dengruixin";
    
   
     static final String USER = "sa";
    
     static final String PASSWORD = "sa";
    

    public static void main(String[] args) throws Exception {
       ;
        
        jdbcDemo();
 
         
         ormDemo("");
    }
    
    public static void ormDemo(String str) throws SQLException{
        ConnectionSource cs =  H2ORMConn.getConn(s);
        Dao<Account,Integer> dao = DaoManager.createDao(cs, Account.class);
        p("========================新建表account");
        TableUtils.dropTable(cs,  Account.class, true);
        TableUtils.createTableIfNotExists(cs, Account.class);
        
        p("staring...=========================插入3条数据 ");
        Account one = new Account("dengruixin","987654");
        Account two = new Account("Taylor Swift","456789");
        Account three = new Account("Taylor Swift6");
        dao.create(one);
        dao.create(two);
        dao.create(three);
        List<Account> l=showAll(dao);
        Account first = l.get(0);
        
        p("update ==================================="+first.getName()+"===>"+"邓蕊欣");
        first.setName("邓蕊欣");
        dao.update(first);
        showAll(dao);
        
        p("delete..================================删除  邓蕊欣");
        dao.delete(first);
        
        showAll(dao);
        
       
        query(dao);
        
        
    }
    
   public static void query(Dao dao) throws SQLException{
       QueryBuilder qu = dao.queryBuilder();
       SelectArg arg = new SelectArg();
       qu.where().like("name", arg);
       PreparedQuery pq = qu.prepare();
       arg.setValue("%baby%");
       List l=dao.query(pq);
       for(Object o :l){
           Account a =(Account)o;
           p(a.getName()+"");
       }
   }
    
    public static void jdbcDemo() throws Exception{
      
        Class.forName(DRIVER_CLASS);
  
        Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
    
        Statement stmt = conn.createStatement();
    
         stmt.execute("DROP TABLE IF EXISTS stu_info");
       
         stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");

        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','邓蕊欣','女','20151102009')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','农时月','女','20151102011')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','银杉','女','20151102006')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','黄文君','女','20151102004')");
       
        p("++++++++++++++++++++++++++++++++++++++显示刚保存的四条数据：");
        show(stmt);
        p("");
        p(""); 
        p("");   
        stmt.executeUpdate("DELETE FROM stu_info WHERE name='黄文君'");
        p("++++++++++++++++++++++++++++++++++++++删除 黄文君 后，显示结果：");
        show(stmt);
        
        p("");
        p("");
        stmt.executeUpdate("UPDATE stu_info SET name='农时越' WHERE name='农时月'");
        p("=+++++++++++++++++++++++++++++++++修改 农时月 === >农时越,显示结果：");
        show(stmt);
      
        
        
       
        stmt.close();
        
        
        
        conn.close();
        System.out.println("------jdbc -end!!!------");

    }
    
     
    
    // ++++++++++++++++++++++++++++++++ormlite+++++++++++++++++++++++++++++++++++++++++
    
    public static void ormDemo(){
        p("");
        p("");
        p("");
        p("");
        p("++++++++++++++++++++++++以下是ormlite 操作H2 ++++++++++++++++++++++++");
        StudentDAO studentDAOUtil =new StudentDAO(s);
        StudentService service = new StudentService(studentDAOUtil.getStudentDAO());
      
        StudentInfo first = showAll(service).get(0);
        p("");
        p("");
        p("===========================更新第一条数据：姓名 "+first.getName()+" 改成 范冰冰");
        first.setName("范冰冰");
     
        service.update(first);
    
        showAll(service);
        p("");
        p("");
        p("============================删除第一条数据："+first.getName());
    
        service.del(first);
  
        showAll(service);

        p("");
        p("");
        p("============================增加2条数据：" );
        StudentInfo one = new StudentInfo();
        one.setId(7);
        one.setName("陈奕迅");
        one.setSex("男");
        one.setStudentId("19845870394");
        service.save(one);
        
        StudentInfo two = new StudentInfo();
        two.setId(8);
        two.setName("小曲儿");
        two.setSex("男");
        two.setStudentId("1986386459");
        service.save(two);
        showAll(service);
        
        studentDAOUtil.close();
        
    }
    
    public static List<StudentInfo> showAll(StudentService service){
        p("=============================显示目前学生表的所有的记录： ");
        List<StudentInfo> list =  service.queryAll();
        for(StudentInfo info:list){
            p(""+info.getId()+"\t"+info.getName()+"\t"+info.getSex()+"\t"+info.getStudentId());
        }
        return list;
    }
    
     public static List<Account> showAll(Dao dao) throws SQLException{
        p("=============================显示account的所有的记录： ");
        List<Account> list =  dao.queryForAll();
        for(Account info:list){
            p(""+info.getId()+"\t"+info.getName()+"\t"+info.getPassword() );
        }
        return list;
    }
    
    
     static void p(String a){
        System.out.println(a);
    }
    
    //遍历结果集
    private static void show( Statement stmt) throws Exception{
        //查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
          while (rs.next()) {
            p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString(4));
        }
    }
    static Server s=null;
    private static void  initServer(){
        try { 
            s=  Server.createTcpServer().start();
            p(" create H2 server successfully!!!!!!! ");
        } catch (SQLException ex) {
            Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
            p(" create H2 server failure ");
        }
        
    }
    private static int num =1;
    private static int getId(){
        return num++  ;
    }
    
}
    

